package cn.waka.sys.admin.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang.StringUtils;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Component;

import cn.waka.sys.admin.object.Dic;

@Component
public class DicDao extends BaseDao {
	//根据父ID查询
		public List<Map<String,Object>> getDicList(int pid){	
			String sql = "SELECT id,`value`,icon FROM w_dic WHERE status=1 and pid=? ORDER BY sort DESC";

			List<Map<String,Object>> list = this.query(sql, new RowMapper<Map<String,Object>>() {
				@Override
				public Map<String,Object> mapRow(ResultSet rs, int rowNum) throws SQLException {
					Map<String,Object> row=new HashMap<String,Object>();
					row.put("id", rs.getInt(1));
					row.put("name", rs.getString(2));
					row.put("img", rs.getString(3));
					return row;
				}
			},pid);

			return list;
		}
	//根据父ID查询
	public List<Map<String,Object>> getTypeList(int pid){	
		String sql = "SELECT id,`value`,pid,icon,`status`,`key` FROM w_dic WHERE pid=? ORDER BY sort DESC";

		List<Map<String,Object>> list = this.query(sql, new RowMapper<Map<String,Object>>() {
			@Override
			public Map<String,Object> mapRow(ResultSet rs, int rowNum) throws SQLException {
				Map<String,Object> row=new HashMap<String,Object>();
				row.put("id", rs.getInt(1));
				row.put("value", rs.getString(2));
				row.put("pid", rs.getInt(3));
				row.put("icon", rs.getString(4));
				row.put("status", rs.getInt(5));
				row.put("key", rs.getString(6));
				return row;
			}
		},pid);

		return list;
	}
	//根据KEY模糊查询
	public List<Map<String,Object>> getDicByKey(String key){
		String k = key+",%"; 
		String sql = "SELECT id,`value`,icon,sort FROM w_dic w WHERE w.key LIKE ?";
		
		List<Map<String,Object>> list = this.query(sql, new RowMapper<Map<String,Object>>() {
			@Override
			public Map<String,Object> mapRow(ResultSet rs, int rowNum) throws SQLException {
				Map<String,Object> row=new HashMap<String,Object>();
				row.put("id", rs.getInt(1));
				row.put("name", rs.getString(2));
				row.put("img", rs.getString(3));
				row.put("sort", rs.getString(4));
				return row;
			}
		},k);
		return list;
	}

	//根据本ID查询
	public Dic getDicById(final int id) {
		String sql = "SELECT id,`value`,pid,icon,sort,`key` FROM w_dic w WHERE id = ?";

		List<Dic> list = this.query(sql, new RowMapper<Dic>() {

			@Override
			public Dic mapRow(ResultSet rs, int rowNum) throws SQLException {
			
				Dic dic = new Dic();
				dic.setId(id);
				dic.setName(rs.getString(2));
				dic.setPid(rs.getInt(3));
				dic.setIcon(rs.getString(4));
				dic.setSort(rs.getInt(5));
				dic.setKey(rs.getString(6));
				return dic;

			}

		}, id);

		return list.size() > 0 ? list.get(0) : null;
	}
	
	//禁用/启用
	public int isDisable(int id,int status) {
		String usql = "update w_dic set status=? where id=?";
		int rs = this.update(usql, status,id);
		return rs;
	}
	
	//删除一个
	public int deleteDic(int id) {
		String sql = "DELETE FROM `w_dic` WHERE id =?";
		int rs = this.update( sql, id);
		return rs;
	}
	
	//根据KEY模糊查询
	public List<Dic> getDicByPid(final int pid) {
		String sql = "SELECT id,`value`,pid,icon,`status`,`key` FROM w_dic WHERE FIND_IN_SET(?,`key`) AND id!=? ORDER BY pid,sort DESC";
		final Map<Integer,Dic> msAll=new LinkedHashMap<Integer,Dic>();
		this.query(sql, new RowMapper<Dic>() {
			@Override
			public Dic mapRow(ResultSet rs, int rowNum) throws SQLException {
			
				Dic dic = new Dic();
				dic.setId(rs.getInt(1));
				dic.setName(rs.getString(2));
				dic.setPid(rs.getInt(3));
				dic.setIcon(rs.getString(4));
				dic.setStatus(rs.getInt(5));
				dic.setKey(rs.getString(6));
				msAll.put(rs.getInt(1), dic);
				return dic;

			}

		}, new Object[]{pid,pid});

		List<Dic> ms=new ArrayList<Dic>();
		for (Dic m : msAll.values()) { 
			if(m.getPid()<=pid){
				ms.add(m);
			}else{
				msAll.get(m.getPid()).add(m);
			}
		} 
		
		int n = ms.get(0).getKey().split(",").length;
		ms = arrTree(ms,n);
		
		return ms;
	}

	//根据KEY模糊查询
		public List<Dic> getDicMenu(final int pid, final int id) {
			String sql = "SELECT id,`value`,pid,icon,`status`,`key` FROM w_dic WHERE FIND_IN_SET(?,`key`) AND !FIND_IN_SET(?, `key`) ORDER BY pid,sort DESC";
			final Map<Integer,Dic> msAll=new LinkedHashMap<Integer,Dic>();
			this.query(sql, new RowMapper<Dic>() {
				@Override
				public Dic mapRow(ResultSet rs, int rowNum) throws SQLException {
				
					Dic dic = new Dic();
					dic.setId(rs.getInt(1));
					dic.setName(rs.getString(2));
					dic.setPid(rs.getInt(3));
					dic.setIcon(rs.getString(4));
					dic.setStatus(rs.getInt(5));
					dic.setKey(rs.getString(6));
					msAll.put(rs.getInt(1), dic);
					return dic;

				}

			}, new Object[]{pid,id});

			List<Dic> ms=new ArrayList<Dic>();
			for (Dic m : msAll.values()) { 
				if(m.getPid()<=pid){
					ms.add(m);
				}else{
					msAll.get(m.getPid()).add(m);
				}
			} 
			
			int n = ms.get(0).getKey().split(",").length;
			ms = arrTree(ms,n);
			
			return ms;
		}
		
	//树形结构数据转换成list
	public List<Dic> arrTree(List<Dic> list,int n) {
		List<Dic> arrlist = new ArrayList<Dic>();
		for(Dic d : list){
			int k = d.getKey().split(",").length;
			String spl= StringUtils.repeat("&nbsp;&nbsp;&nbsp;├&nbsp;&nbsp;",k-n);
			d.setSpl(spl);
			arrlist.add(d);
			if(d.getChildren()!=null){
				List<Dic> _arrlist = arrTree(d.getChildren(),n);
				arrlist.addAll(_arrlist);
			}
		}
		
		return arrlist;
	}
	
	public int insert(final Dic r) {
		KeyHolder holder = new GeneratedKeyHolder();
		this.update(new PreparedStatementCreator() {

			@Override
			public PreparedStatement createPreparedStatement(Connection con) throws SQLException {
				String sql = "insert into w_dic(pid,`value`,icon)values(?,?,?)";
				PreparedStatement ps = con.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
				ps.setInt(1, r.getPid());
				ps.setString(2, r.getName());
				ps.setString(3, r.getIcon());
				return ps;
			}
		}, holder);
		int id = holder.getKey().intValue();
		return id;
	}
	
	public int updateDIc(Dic r) {
		int rs = this.update("update w_dic set `pid`=?,`value`=?,icon=?,`key`=? where id=?",
				r.getPid(),
				r.getName(),
				r.getIcon(),
				r.getKey(),
				r.getId());
		return rs;
	}
	
	public int updateKey(int id, String key) {
		int rs = this.update("update w_dic set `key`=? where id=?",key,id);
		return rs;
	}
	
	public int delDicTree(int id) {
		String sql = "DELETE FROM `w_dic` WHERE FIND_IN_SET(?,`key`)";
		int rs = this.update( sql, id);
		return rs;
	}
	
	//根据本ID查询
	public Map<Integer,String> getDicMap() {
		String sql = "SELECT id,`value`,pid,icon,sort FROM w_dic ";
		Map<Integer,String> dicMap=new HashMap<Integer,String>();
		this.query(sql, new RowMapper<Integer>() {
			@Override
			public Integer mapRow(ResultSet rs, int rowNum) throws SQLException {
				dicMap.put(rs.getInt(1), rs.getString(2));
				return rs.getInt(1);
			}

		});
		return dicMap;
	}
}
